﻿Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports BusinessRule.IBusiness
Imports DatabaseAccess.IDAL
Imports System.Data
Imports BusinessRule.Common
Imports DatabaseAccess.DALFactory
Imports External.APICalling
Imports BusinessRule.DTO
Imports System.Configuration
Imports DatabaseAccess.DBUtility

Public Class uMES_bjCommonBusiness
    Implements uMES_bjCommonIBusiness

    Private dal As IOracleDAL = uMESDalContext.instance.GetDalObject()

    ''' <summary>
    ''' 获取工号集合
    ''' </summary>
    Function GetProcessNoList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjCommonIBusiness.GetProcessNoList
        Dim oResult As New uMESProcessResult
        Try
            Dim strSQL As String
            strSQL = "select distinct processno from Product where processno is not null "
            Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function


#Region "插入历史记录"
    'Public Function InsertContainerOpHistory(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.InsertContainerOpHistory
    '    Dim intSeq As Integer = 0
    '    Dim strSql As String = ""
    '    strSql = String.Format("select NVL(max(t.WORKFLWOSEQ),0) seq from containerophistory t where t.contanername='{0}'", dto.ContanerName)
    '    Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
    '    If dtMain.Rows.Count > 0 Then
    '        intSeq = Convert.ToInt32(dtMain.Rows(0)("seq"))
    '    End If
    '    intSeq = intSeq + 1

    '    strSql = "select wt.sequence,wt.specid,wt.sequence,sb.specname,sb.revofrcdid,sb1.specname as specname1 from workflowstep wt " & vbCrLf & _
    '             "left join specbase sb on sb.specbaseid=wt.specbaseid " & vbCrLf & _
    '             "left join spec s on s.specid=wt.specid " & vbCrLf & _
    '             "left join specbase sb1 on sb1.specbaseid=s.specbaseid " & vbCrLf & _
    '             "where  wt.workflowid='" & dto.WorkFlowID & "' order by wt.sequence"
    '    Dim stepTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

    '    For i As Integer = 0 To stepTable.Rows.Count - 1
    '        Dim strSpecID As String = ""
    '        If stepTable.Rows(i)("specid").ToString() = "0000000000000000" Then
    '            strSpecID = stepTable.Rows(i)("revofrcdid").ToString().Trim()
    '        Else
    '            strSpecID = stepTable.Rows(i)("specid").ToString().Trim()
    '        End If
    '        Dim strSpecName As String = ""
    '        If stepTable.Rows(i)("specname").ToString().Trim() = "" Then
    '            strSpecName = stepTable.Rows(i)("specname1").ToString().Trim()
    '        Else
    '            strSpecName = stepTable.Rows(i)("specname").ToString().Trim()
    '        End If
    '        'intSeq = intSeq + i
    '        Dim strInsert As String = String.Format("insert into containerophistory (contanername, specid, specseq, specname, WORKFLOWID,WORKFLWOSEQ) " & vbCrLf & _
    '                                       "  values ('{0}', '{1}', {2}, '{3}', '{4}',{5})", dto.ContanerName, strSpecID, Convert.ToInt32(stepTable.Rows(i)("sequence")), strSpecName,
    '                                        dto.WorkFlowID, intSeq)
    '        DatabaseHelper.ExecuteSql(strInsert)
    '    Next
    '    InsertContainerOpHistory = True
    'End Function
#End Region

#Region "插入一道独立的工序在历史记录"
    'Public Function InsertHistory(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.InsertHistory

    '    Dim strSql As String = String.Format("select WORKFLWOSEQ from containerophistory t where t.contanername='{0}'", dto.ContanerName)
    '    Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
    '    Dim intSeq As Integer = Convert.ToInt32(dtMain.Rows(0)("WORKFLWOSEQ"))

    '    Dim strInsert As String = String.Format("insert into containerophistory (contanername, specid, specseq, specname, WORKFLOWID,WORKFLWOSEQ) " & vbCrLf & _
    '                                      "  values ('{0}', '{1}', {2}, '{3}', '{4}',{5})", dto.ContanerName, dto.SpecID, dto.Seq, dto.SpecName, dto.WorkFlowID, intSeq)
    '    DatabaseHelper.ExecuteSql(strInsert)
    '    InsertHistory = True
    'End Function
#End Region

#Region "查找批次历史记录"
    'Public Function SelectContianerOpHistory(ByVal dto As ContanerHistoryDTO) As DataTable Implements uMES_bjCommonIBusiness.SelectContianerOpHistory
    '    Dim strSql As String = String.Format("select t.* from containerophistory t where t.contanername='{0}' and t.specname='{1}'", dto.ContanerName, dto.SpecName)
    '    Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
    '    SelectContianerOpHistory = dtMain
    'End Function
#End Region

#Region "查找工艺路线是否存在"
    'Public Function SelectWorkFlowOpHistory(ByVal dto As ContanerHistoryDTO) As DataTable Implements uMES_bjCommonIBusiness.SelectWorkFlowOpHistory
    '    Dim strSql As String = String.Format("select t.* from containerophistory t where t.workflowid='{0}' and  t.contanername='{1}'", dto.WorkFlowID, dto.ContanerName)
    '    Dim dtMain As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
    '    SelectWorkFlowOpHistory = dtMain
    'End Function
#End Region

#Region "派工修改派工数量"
    'Public Function UpdateHistoryDataBydispatch(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataBydispatch
    '    Dim strSql As String = "update containerophistory  set  dispatchqty =NVL(dispatchqty, 0) +" & dto.DispatchQty & " where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataBydispatch = True
    'End Function
#End Region

#Region "派工修改派工数量"
    'Public Function UpdateHistoryDataBydispatchTwoLevel(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataBydispatchTwoLevel
    '    Dim strSql As String = "update containerophistory  set  dispatchqty2 =NVL(dispatchqty2, 0) +" & dto.DispatchQty2 & " where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataBydispatchTwoLevel = True
    'End Function
#End Region

#Region "修改合格不合格数量"
    'Public Function UpdateHistoryDataByCheck(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataByCheck
    '    Dim strSql As String = "update containerophistory  set  ELIGIBLEQTY =NVL(ELIGIBLEQTY, 0) +" & dto.EligibleQty & ",NONSENSEQTY=NVL(NONSENSEQTY,0)+" & dto.NonSenseQty & " where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataByCheck = True
    'End Function
#End Region

#Region "报工修改报工数量"
    'Public Function UpdateHistoryDataByReport(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataByReport
    '    Dim strSql As String = "update containerophistory  set  REPORTQTY =NVL(REPORTQTY, 0) +" & dto.ReportQty & " where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataByReport = True
    'End Function
#End Region

#Region "修改报废数量"
    'Public Function UpdateHistoryDataByScray(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataByScray
    '    Dim strSql As String = "update containerophistory  set  SCRAPQTY =NVL(SCRAPQTY, 0) +" & dto.ScrapQty & " where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataByScray = True
    'End Function
#End Region

#Region "修改质量记载单数量"
    'Public Function UpdateHistoryDataByQuality(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataByQuality
    '    Dim strSql As String = "update containerophistory  set  QUALITYQTY =" & dto.QualityQty & " where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataByQuality = True
    'End Function
#End Region

#Region "报工回滚修改日志表报工数量"
    Public Function UpdateReversalByreport(ByVal strHistoryID As String) As Boolean Implements uMES_bjCommonIBusiness.UpdateReversalByreport
        Dim strSql As String = "select c.containername,sb.specname,wch.containerid,wch.specid,t.* from workingreporthistory t " & vbCrLf & _
                               "left join historymainline h on h.historymainlineid=t.historymainlineid " & vbCrLf & _
                               "left join workcenterdispatching wch on wch.workcenterdispatchingid=t.workcenterdispatchingid " & vbCrLf & _
                               "left join container c on c.containerid=wch.containerid " & vbCrLf & _
                               "left join spec s on s.specid=wch.specid " & vbCrLf & _
                               "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
                               "where h.historymainlineid='" & strHistoryID & "'"

        Dim hsitroyTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        hsitroyTable.TableName = "hsitroyTable"

        Dim intQty As Integer = Convert.ToInt32(hsitroyTable.Rows(0)("qty"))
        Dim strContainerName As String = hsitroyTable.Rows(0)("containername").ToString().Trim()
        Dim strSpecName As String = hsitroyTable.Rows(0)("specname").ToString().Trim()

        strSql = "update containerophistory  set  REPORTQTY =NVL(REPORTQTY, 0) -" & intQty & " where contanername ='" & strContainerName & "' and specname ='" & strSpecName & "'"
        DatabaseHelper.ExecuteSql(strSql)
        UpdateReversalByreport = True
    End Function
#End Region

#Region "常规检验数据回滚"
    Public Function UpdateReversalByCheck(ByVal strHistoryID As String) As Boolean Implements uMES_bjCommonIBusiness.UpdateReversalByCheck
        Dim strSql As String = "select c.containername,sb.specname,t.containerid,t.specid,t.* from conventioncheckhistory t " & vbCrLf & _
                               "left join historymainline h on h.historymainlineid=t.historymainlineid " & vbCrLf & _
                               "left join container c on c.containerid=t.containerid " & vbCrLf & _
                               "left join spec s on s.specid=t.specid " & vbCrLf & _
                               "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
                               "where h.historymainlineid='" & strHistoryID & "'"

        Dim hsitroyTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        hsitroyTable.TableName = "hsitroyTable"

        Dim intEligibilityqty As Integer = Convert.ToInt32(hsitroyTable.Rows(0)("eligibilityqty")) '合格数
        Dim intNonsenseqty As Integer = Convert.ToInt32(hsitroyTable.Rows(0)("nonsenseqty"))       '不合格数
        Dim strContainerName As String = hsitroyTable.Rows(0)("containername").ToString().Trim()
        Dim strSpecName As String = hsitroyTable.Rows(0)("specname").ToString().Trim()

        strSql = "update containerophistory  set  ELIGIBLEQTY =NVL(ELIGIBLEQTY, 0) -" & intEligibilityqty & ",NONSENSEQTY=NVL(NONSENSEQTY,0) -" & intNonsenseqty & " where contanername ='" & strContainerName & "' and specname ='" & strSpecName & "'"
        DatabaseHelper.ExecuteSql(strSql)
        UpdateReversalByCheck = True
    End Function

#End Region



#Region "修改数量"
    'Public Function UpdateHistoryDataByLastSpec(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataByLastSpec

    '    Dim strSql As String = "update containerophistory  set  STATE=1 where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataByLastSpec = True
    'End Function
#End Region

#Region "转出修改"
    'Public Function UpdateHistoryDataBySynergInfo(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataBySynergInfo

    '    Dim strSql As String = "update containerophistory  set  dispatchqty =NVL(dispatchqty, 0) +" & dto.DispatchQty & ",RETURNQTY =NVL(RETURNQTY, 0) +" & dto.Returnqty & vbCrLf & _
    '                           " where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataBySynergInfo = True
    'End Function

    'Public Function UpdateHistoryDataBySynergInfoUpdate(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataBySynergInfoUpdate

    '    Dim strSql As String = "update containerophistory  set  dispatchqty =" & dto.DispatchQty & ",RETURNQTY =" & dto.Returnqty & vbCrLf & _
    '                           " where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataBySynergInfoUpdate = True
    'End Function
#End Region

#Region "转入修改"
    'Public Function UpdateHistoryDataBySynergReturnInfo(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataBySynergReturnInfo
    '    Dim strSql As String = "update containerophistory  set  REPORTQTY =NVL(REPORTQTY, 0) +" & dto.ReportQty & ",STATE=" & dto.State & "  where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataBySynergReturnInfo = True
    'End Function
#End Region

#Region "得到当前在制批次和零件进度表内容"
    Public Function GetWorkRportInfo(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjCommonIBusiness.GetWorkRportInfo
        Dim strSql As String = "select c.containerid,c.containername,c.qty ,pb.productname,p.processno,p.description from container c " & vbCrLf & _
                               "left join product p on p.productid=c.productid " & vbCrLf & _
                               "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf & _
                               "where c.status=1 and c.parentcontainerid is null and c.containerid<>c.containername"

        If QueryCondition.Keys.Contains("containername") = True Then
            strSql += " AND LOWER(c.containername) like '%" & QueryCondition("containername").ToString.ToLower & "%'" & vbCrLf
        End If
        If QueryCondition.Keys.Contains("productname") = True Then
            strSql += " AND LOWER(pb.productname) like '%" & QueryCondition("productname").ToString.ToLower & "%'" & vbCrLf
        End If
        If QueryCondition.Keys.Contains("productdesp") = True Then
            strSql += " AND LOWER(p.description) like '%" & QueryCondition("productdesp").ToString.ToLower & "%'" & vbCrLf
        End If
        If QueryCondition.Keys.Contains("processno") = True Then
            strSql += " AND LOWER(p.processno) like '%" & QueryCondition("processno").ToString.ToLower & "%'" & vbCrLf
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = strSql
        Dim result As New uMESProcessResult
        result = dal.RunProcedureForPage(paraPage)

        Dim tmpTable As DataTable = result.DBTable.Copy()
        result.DBTable.TableName = "containerName"
        'result.DBTable = DataAdjust.FormatDataContainerNameByColumNameBJ("containerName", result.DBTable)
        result.DBDataSet.Tables.Add(result.DBTable.Copy())

        Dim strContainerName As String = ""
        For i = 0 To tmpTable.Rows.Count - 1
            strContainerName = strContainerName & tmpTable.Rows(i).Item("containerName") & "','"
        Next
        If strContainerName.Trim() <> "" Then
            strContainerName = strContainerName.Substring(0, strContainerName.Length - 3)
        End If
        strSql = "select t.* from containerophistory t where t.contanername in ('" & strContainerName & "') "

        Dim hsitroyTable As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        hsitroyTable.TableName = "hsitroyTable"

        'hsitroyTable = DataAdjust.FormatDataContainerNameByColumNameBJ("CONTANERNAME", hsitroyTable)
        hsitroyTable = DataAdjust.FormatDataSetSpecNameByColumName("SpecName", hsitroyTable)
        result.DBDataSet.Tables.Add(hsitroyTable.Copy())

        result.Result = True
        Return result
    End Function
#End Region


    ''' <summary>
    ''' 获取计量单位集合
    ''' </summary>
    Public Function GetUOMList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjCommonIBusiness.GetUOMList
        Dim oResult As New uMESProcessResult
        Try
            Dim strSQL As String
            strSQL = "select t.uomid,t.uomname from uom t "
            Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    Public Function GetPriorityCodeList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjCommonIBusiness.GetPriorityCodeList
        Dim oResult As New uMESProcessResult
        Try
            Dim strSQL As String
            strSQL = "select t.prioritycodeid,t.prioritycodename from prioritycode t "
            Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)

            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    Public Function GetOrderTypeList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjCommonIBusiness.GetOrderTypeList
        Dim oResult As New uMESProcessResult
        Try
            Dim strSQL As String
            strSQL = "select t.ordertypeid,t.ordertypename from ordertype t "
            Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 获取车间集合
    ''' </summary>
    Public Function GetFactoryList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjCommonIBusiness.GetFactoryList
        Dim oResult As New uMESProcessResult
        Try
            Dim strSQL As String
            strSQL = "select t.factoryid,t.factoryname,t.description from factory t "
            Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 根据角色名称和车间获取Employee
    ''' </summary>
    Public Function GetEmployeeByRoleAndFactory(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uMES_bjCommonIBusiness.GetEmployeeByRoleAndFactory
        Dim oResult As New uMESProcessResult
        Try
            Dim strSQL As String
            strSQL = "select t.employeename,t.fullname,f.factoryname,r.rolename from employee t " & vbCrLf & _
                    "left join sessionvalues sv on sv.sessionvaluesid=t.sessionvaluesid" & vbCrLf & _
                    "left join factory f on f.factoryid=sv.factoryid" & vbCrLf & _
                    "left join employeerole er on er.employeeid=t.employeeid" & vbCrLf & _
                    "left join roledef r on r.roleid = er.roleid" & vbCrLf & _
                    "where 1=1 "
            If para.Keys.Contains("rolename") = True Then
                strSQL += " AND r.rolename ='" + para("rolename").ToString() + "'"
            End If
            If para.Keys.Contains("factoryname") = True Then
                strSQL += " AND f.factoryname ='" + para("factoryname").ToString() + "'"
            End If

            Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
            oResult.DBTable = dtMain
            oResult.Result = True
            Return oResult
        Catch ex As Exception
            oResult.Result = False
            oResult.Message = ex.Message
            Return oResult
        End Try
    End Function

    ''' <summary>
    ''' 获取子批次号集合
    ''' </summary>
    Public Function GetProdutNo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjCommonIBusiness.GetProdutNo
        Dim strSQL As String
        strSQL = "select chc.containername,chc.status from container c " & vbCrLf & _
                    "left join container chc on chc.parentcontainerid = c.containerid" & vbCrLf & _
                    "where 1=1 and chc.status<>2"
        If para.Keys.Contains("containername") = True Then
            strSQL += " AND c.containername ='" + para("containername").ToString() + "'"
        End If

        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function

    ''' <summary>
    ''' 获取批次变更原因
    ''' </summary>
    Public Function GetChangeStatusReason(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjCommonIBusiness.GetChangeStatusReason
        Dim strSQL As String
        strSQL = "select t.changestatusreasonname from changestatusreason t "

        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function
    ''' <summary>
    ''' 获取工艺路线工步 add by ljq 2015-11-19
    ''' </summary>
    Public Function GetWorkflowStep(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjCommonIBusiness.GetWorkflowStep
        Dim strSQL As String
        If para("workflowror").ToString = "1" Then
            '默认版本
            strSQL = "select wfs.workflowstepname,wfs.sequence,w.workflowrevision,w.workflowid from workflowbase wb" & vbCrLf & _
                    "left join workflow w on w.workflowid = wb.revofrcdid" & vbCrLf & _
                    "left join workflowstep wfs on wfs.workflowid = w.workflowid" & vbCrLf & _
                    "where 1=1"
        Else
            '非默认版本
            strSQL = "select wfs.workflowstepname,wfs.sequence,w.workflowrevision,w.workflowid from workflowbase wb" & vbCrLf & _
                    "left join workflow w on w.workflowbaseid = wb.workflowbaseid" & vbCrLf & _
                    "left join workflowstep wfs on wfs.workflowid = w.workflowid" & vbCrLf & _
                    "where 1=1"
            '工艺路线版本号
            If para.Keys.Contains("workflowrevision") = True Then
                strSQL += " AND w.workflowrevision ='" + para("workflowrevision").ToString() + "'"
            End If
        End If
        '工艺路线名称
        If para.Keys.Contains("workflowname") = True Then
            strSQL += " AND wb.workflowname ='" + para("workflowname").ToString() + "'"
        End If

        strSQL += " order by wfs.sequence asc"

        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function

    ''' <summary>
    ''' 获取工艺路线工步 add by ljq 2015-11-19
    ''' </summary>
    Public Function GetWorkflowStep1(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjCommonIBusiness.GetWorkflowStep1
        Dim strSQL As String
        strSQL = "select sb.specname,s.specrevision,wfb.workflowname,wf.workflowrevision,wt.workflowstepname,wt.sequence from workflowstep wt" & vbCrLf & _
                    "inner join workflow wf on wf.workflowid=wt.workflowid" & vbCrLf & _
                    "inner join workflowbase wfb on wfb.workflowbaseid=wf.workflowbaseid" & vbCrLf & _
                    "inner join spec s on s.specid=(CASE WHEN wt.specid = '0000000000000000' THEN (SELECT sb1.revofrcdid FROM specbase sb1 WHERE sb1.specbaseid = wt.specbaseid) ELSE wt.specid END) " & vbCrLf & _
                    "left join specbase sb on sb.specbaseid=s.specbaseid " & vbCrLf & _
                    "where 1=1"

        '工艺路线名称
        If para.Keys.Contains("workflowname") = True Then
            strSQL += " AND wfb.workflowname ='" + para("workflowname").ToString() + "'"
        End If

        '工艺路线版本号
        If para.Keys.Contains("workflowrevision") = True Then
            strSQL += " AND wf.workflowrevision ='" + para("workflowrevision").ToString() + "'"
        End If

        '工序名称
        If para.Keys.Contains("specname") = True Then
                strSQL += " AND sb.specname ='" + para("specname").ToString() + "'"
        End If

        '工序版本号
        If para.Keys.Contains("specrevision") = True Then
            strSQL += " AND s.specrevision ='" + para("specrevision").ToString() + "'"
        End If

        strSQL += " order by wt.sequence asc"

        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function


#Region "根据零件图号获取工艺路线信息"
    Public Function GetWorkFlowByProduct(ByVal strProductName As String, ByVal strProductVersion As String) As DataTable Implements uMES_bjCommonIBusiness.GetWorkFlowByProduct
        Dim strSql As String = "select wb.workflowname,w.workflowrevision,w.workflowid,wb1.workflowname as workflowname1,wb1.revofrcdid,w1.workflowrevision as workflworevision1," & vbCrLf & _
                               "pb.productname,p.productrevision from product p " & vbCrLf & _
                               "left join workflow w on w.workflowid=p.workflowid " & vbCrLf & _
                               "left join workflowbase wb on wb.workflowbaseid=w.workflowbaseid " & vbCrLf & _
                               "left join workflowbase wb1 on wb1.workflowbaseid=p.workflowbaseid " & vbCrLf & _
                               "left join workflow w1 on w1.workflowid=wb1.revofrcdid " & vbCrLf & _
                               "left join productbase pb on pb.productbaseid=p.productbaseid " & vbCrLf & _
                               "where pb.productname='" & strProductName & "'  and p.productrevision='" & strProductVersion & "'"

        Dim workFlowTable As DataTable = dal.StandardGetDataTable(strSql)

        For Each row As DataRow In workFlowTable.Rows
            If row.IsNull("workflowname1") = False And row("workflowname1").ToString().Trim() <> "" Then
                row("workflowname") = row("workflowname1")
            End If
            If row.IsNull("workflworevision1") = False And row("workflworevision1").ToString().Trim() <> "" Then
                row("workflowrevision") = row("workflworevision1")
            End If
            If row.IsNull("revofrcdid") = False Then
                row("workflowid") = row("revofrcdid")
            End If
        Next
        Return workFlowTable
    End Function
#End Region

#Region "根据WorkFlowName和版本获取ID"
    Public Function GetWorkFlowIDByName(ByVal strWorkFlowName As String, ByVal strversion As String) As uMESProcessResult Implements uMES_bjCommonIBusiness.GetWorkFlowIDByName

        Dim strQuery As String = "SELECT wb.workflowname,w.workflowrevision,w.description,w.workflowid FROM workflow w " & vbCrLf & _
                                 "LEFT JOIN workflowbase wb ON wb.workflowbaseid=w.workflowbaseid " & vbCrLf & _
                                 "WHERE wb.workflowname = '" & strWorkFlowName & "' and  w.workflowrevision='" & strversion & "'"

        Dim result As New uMESProcessResult

        Try
            Dim DT As DataTable = dal.GetWorkFlowInfoByName(strQuery)
            result.Result = True
            result.DBTable = DT
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try

        Return result
    End Function
#End Region

    

    ''' <summary>
    ''' 产品是否编号：返回值1是，0否
    ''' </summary>
    Public Function GetProductIsNumber(ByVal para As Dictionary(Of String, String)) As Integer Implements uMES_bjCommonIBusiness.GetProductIsNumber
        Dim strSQL As String
        strSQL = "SELECT p.isnumber FROM product p  " & vbCrLf & _
                    "INNER JOIN productbase pb on pb.productbaseid= p.productbaseid" & vbCrLf & _
                    "where 1=1 "
        If para.Keys.Contains("productnamesingle") = True Then
            strSQL += " AND pb.productname ='" + para("productnamesingle").ToString() + "'"
        End If

        If para.Keys.Contains("processnosingle") = True Then
            strSQL += " AND p.processno ='" + para("processnosingle").ToString() + "'"
        End If

        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)

        If dtMain.Rows(0)("isnumber").ToString = "1" Then
            Return 1
        Else
            Return 0
        End If

    End Function


#Region "修改派工数量"
    'Public Function UpdateHistoryDataBydispatchOneLevel1(ByVal dto As ContanerHistoryDTO) As Boolean Implements uMES_bjCommonIBusiness.UpdateHistoryDataBydispatchOneLevel1
    '    Dim strSql As String = "update containerophistory  set  dispatchqty =" & dto.DispatchQty & " where contanername ='" & dto.ContanerName & "' and specname ='" & dto.SpecName & "'"
    '    DatabaseHelper.ExecuteSql(strSql)
    '    UpdateHistoryDataBydispatchOneLevel1 = True
    'End Function
#End Region

    ''' <summary>
    ''' 获取组织结构集合
    ''' </summary>
    Public Function GetOrganizationList() As DataTable Implements uMES_bjCommonIBusiness.GetOrganizationList
        Dim strSql As String = "select t.organizationid,t.organizationname from organization t "
        Dim dtMain As DataTable = dal.StandardGetDataTable(strSql)
        Return dtMain
    End Function

    ''' <summary>
    ''' 获取组织结构集合
    ''' </summary>
    Function GetOrganizationList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjCommonIBusiness.GetOrganizationList
        Dim strSQL As String
        strSQL = "select t.organizationid,t.organizationname from organization t " & vbCrLf & _
                    "left join employee e on e.primaryorganizationid = t.organizationid" & vbCrLf & _
                    "where 1=1 "
        If para.Keys.Contains("employeename") = True Then
            If para("employeename").ToString <> "" Then
                strSQL += " AND lower(e.employeename) ='" + para("employeename").ToString().ToLower + "'"
            End If
        End If
        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function

    Function GetSynergicInfoList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjCommonIBusiness.GetSynergicInfoList
        Dim strSQL As String
        strSQL = "select t.* from synergicinfo t" & vbCrLf & _
                    "left join container c on c.containerid =t.containerid" & vbCrLf & _
                    "left join spec s on s.specid = t.specid" & vbCrLf & _
                    "left join specbase sb on sb.specbaseid=s.specbaseid" & vbCrLf & _
                    "where 1=1 "
        If para.Keys.Contains("containername") = True Then
            If para("containername").ToString <> "" Then
                strSQL += " AND c.containername ='" + para("containername").ToString() + "'"
            End If
        End If

        If para.Keys.Contains("specname") = True Then
            If para("specname").ToString <> "" Then
                strSQL += " AND sb.specname ='" + para("specname").ToString() + "'"
            End If
        End If

        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function

    ''' <summary>
    ''' 获取工人集合
    ''' </summary>
    Function GetToEmployeeList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjCommonIBusiness.GetToEmployeeList
        Dim strSQL As String
        strSQL = "select t.fullname,t.employeename,o.organizationname from employee t " & vbCrLf & _
                    "left join organization o on o.organizationid=t.primaryorganizationid" & vbCrLf & _
                    "left join sessionvalues s on s.employeeid= t.employeeid" & vbCrLf & _
                    "left join factory f on f.factoryid=s.factoryid" & vbCrLf & _
                    "where 1=1 "

        strSQL += " AND o.organizationname ='" + para("organizationname").ToString() + "'"

        If para("organizationname").ToString <> "条件保障处" Then

            strSQL += " AND f.factoryname ='" + para("factoryname").ToString() + "'"
        Else

        End If
        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function

    ''' <summary>
    ''' 获取派工单信息
    ''' </summary>
    Function GetWorkCenterDispatchInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uMES_bjCommonIBusiness.GetWorkCenterDispatchInfo
        Dim strSQL As String
        strSQL = "select c.containername,sb.specname,s.specrevision from workcenterdispatching wcd " & vbCrLf & _
                    "inner join container c on c.containerid = wcd.containerid" & vbCrLf & _
                    "inner join spec s on s.specid=wcd.specid" & vbCrLf & _
                    "inner join specbase sb on sb.specbaseid = s.specbaseid" & vbCrLf & _
                    "where 1=1 "
        If para.Keys.Contains("containername") = True Then
            If para("containername").ToString <> "" Then
                strSQL += " AND c.containername ='" + para("containername").ToString() + "'"
            End If
        End If

        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function

    ''' <summary>
    ''' 获取器材编号集合
    ''' </summary>
    Function GetEquipmentNoList() As DataTable Implements uMES_bjCommonIBusiness.GetEquipmentNoList
        Dim strSQL As String
        strSQL = "select t.equipmentnoid,t.equipmentnoname from equipmentno t "
        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function

    ''' <summary>
    ''' 获取检验器具集合
    ''' </summary>
    Function GetCheckEquipmentList() As DataTable Implements uMES_bjCommonIBusiness.GetCheckEquipmentList
        Dim strSQL As String
        strSQL = "select t.checkequipmentid,t.checkequipmentname from checkequipment t "
        Dim dtMain As DataTable = dal.StandardGetDataTable(strSQL)
        Return dtMain
    End Function
End Class
